package org.nativejpa.support.sqlgen;

import org.nativejpa.mapping.ColumnMetadata;
import org.nativejpa.mapping.EntityRepository;
import org.nativejpa.mapping.EntityMetadata;
import org.nativejpa.mapping.JoinColumnMetadata;

import javax.persistence.Table;
import java.util.Collection;
import java.util.Map;

import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.Log;

/**
 * SQL Generate, can generate insert/update/delete according entity class
 * support simple condition
 *
 * @author <a href="mailto:jfox.young@gmail.com">Young Yang</a>
 * @create Apr 27, 2008 10:58:31 AM
 */
public class SQLGenerator {
    
    final static Log log = LogFactory.getLog(SQLGenerator.class);

    //TODO: cache generated SQL with key: AUTO_%TABLE_NAME%_JOINCOLUMN_ID_ORDERID
    /**
     * build create sql
     * @param entityClass
     * @return sql velocity template
     */
    public static String buildInsertSQL(Class<?> entityClass){
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("INSERT INTO ").append(tableName).append(" (");
        StringBuffer sql2 = new StringBuffer(" VALUES (");

        Collection<ColumnMetadata> columnMetadatas =  EntityRepository.getInstance().getAllColumnMetadatas(entityClass);
        int colIndex = 0;
        for(ColumnMetadata columnMetadata : columnMetadatas){
            String colName = columnMetadata.getName();
            if(colIndex > 0) {
                sql.append(", ");
                sql2.append(", ");
            }
            sql.append(colName);
            if(!columnMetadata.isJoinColumn()) {
                sql2.append("$").append(tableName).append(".").append(columnMetadata.getFieldName());
            }
            else {
                //generate $person.address.@ReferencedFieldName for @JoinColumn
//                String referencedColumnName = ((JoinColumnMetadata)columnMetadata).getReferencedColumnName();
//                String referencedFieldName = EntityRepository.getInstance().getColumnMetadata(columnMetadata.getFieldType(), referencedColumnName).getFieldName();
                sql2.append("$").append(tableName).append(".").append(columnMetadata.getFieldName());
            }
            colIndex++;
        }
        sql.append(")");
        sql2.append(")");
        sql.append(sql2);
        return sql.toString();
    }

    public static String buildDeleteByConditionSQL(Class<?> entityClass, Condition condition) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("DELETE FROM ").append(tableName);
        if(condition != null) {
            sql.append(" WHERE ").append(condition.getSQLTemplateString());
        }
        return sql.toString();
    }

    /**
     * build delete sql
     * @param entityClass
     * @return sql velocity template
     */
    public static String buildDeleteByIdSQL(Class<?> entityClass){
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("DELETE FROM ").append(tableName).append(" WHERE ");
        String pkColumnName = "ID"; // default use ID column as PK
        ColumnMetadata pkColumnMetadata = EntityRepository.getInstance().getPKColumnMetadata(entityClass);
        if(pkColumnMetadata != null) {
            pkColumnName = pkColumnMetadata.getName();
        }
        sql.append(pkColumnName).append(" = ").append("$").append(pkColumnName);
        return sql.toString();
    }

    public static String buildDeleteByColumnSQL(Class entityClass, String... columns) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("DELETE FROM ").append(tableName).append(" WHERE ");
        int colIndex = 0;
        for(String column : columns) {
            if(!EntityRepository.getInstance().isColumnExisted(entityClass, column)){
                log.warn("Column " + column + " doesn't exist in Table " + tableName);
            }
            if(colIndex > 0) {
                sql.append(" AND ");
            }
            sql.append(column).append(" = $").append(column.toUpperCase());
            colIndex++;
        }
        return sql.toString();
        
    }

    public static String buildDeleteByIdInSQL(Class<?> entityClass, long... ids){
        String pkColumnName = "ID";
        ColumnMetadata pkColumnMetadata = EntityRepository.getInstance().getPKColumnMetadata(entityClass);
        if(pkColumnMetadata != null) {
            pkColumnName = pkColumnMetadata.getName();
        }
        String[] colValues = new String[ids.length];
        for(int i=0; i<ids.length; i++){
            colValues [i] = ids[i] + "";
        }
        return buildDeleteByColumnInSQL(entityClass, pkColumnName, colValues );
    }

    public static String buildDeleteByColumnInSQL(Class<?> entityClass, String column, String... colValues){
        String tableName = getTableName(entityClass);
        if(!EntityRepository.getInstance().isColumnExisted(entityClass, column)){
            log.warn("Column " + column + " doesn't exist in Table " + tableName);
        }

        StringBuffer sql = new StringBuffer("DELETE FROM ").append(tableName);
        if(colValues.length > 0) {
            sql.append(" WHERE ").append(column).append(" IN (");
            int colIndex = 0;
            for(String colValue : colValues) {
                if(colIndex > 0) {
                    sql.append(", ");
                }
                sql.append(colValue);
                colIndex++;
            }
            sql.append(" )");
        }
        return sql.toString();
    }

    public static String buildUpdateSQL(Class<?> entityClass) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("UPDATE ").append(tableName).append(" SET ");

        Collection<ColumnMetadata> nonMappedColumnMetadatas =  EntityRepository.getInstance().getAllColumnMetadatas(entityClass);
        int colIndex = 0;
        for(ColumnMetadata columnMetadata : nonMappedColumnMetadatas){
            if(!columnMetadata.isPK()) {
                if(colIndex > 0) {
                    sql.append(", ");
                }

                String colName = columnMetadata.getName();

                if(!columnMetadata.isJoinColumn()) {
                    sql.append(colName).append( " = $").append(tableName).append(".").append(columnMetadata.getFieldName());
                }
                else {
                    //generate $person.address.@ReferencedFieldName for @JoinColumn
//                    String referencedColumnName = ((JoinColumnMetadata)columnMetadata).getReferencedColumnName();
                    sql.append(colName).append(" = $").append(tableName).append(".").append(columnMetadata.getFieldName());
                }
                colIndex++;

            }
        }

        String pkColumnName = "ID";
        String pkColumnFieldName = "id"; // default use ID column as PK
        ColumnMetadata pkColumnMetadata = EntityRepository.getInstance().getPKColumnMetadata(entityClass);
        if(pkColumnMetadata != null) {
            pkColumnName = pkColumnMetadata.getName();
            pkColumnFieldName = pkColumnMetadata.getFieldName();
        }
        sql.append(" WHERE ").append(pkColumnName).append(" = ").append("$").append(tableName).append(".").append(pkColumnFieldName);
        return sql.toString();
    }

    public static String buildUpdateInSQL(Class<?> entityClass, String[] colNames, long... ids){
        String tableName = getTableName(entityClass);
        for(String column : colNames){
            if(!EntityRepository.getInstance().isColumnExisted(entityClass, column)){
                log.warn("Column " + column + " doesn't exist in Table " + tableName);
            }
        }

        StringBuffer sql = new StringBuffer("UPDATE ").append(tableName).append(" SET ");

        if(colNames.length > 0) {
            int colIndex = 0;
            for(String colName : colNames) {
                if(colIndex > 0) {
                    sql.append(", ");
                }
                sql.append(colName).append("=").append("$").append(colName);
                colIndex++;
            }
        }

        if(ids.length > 0) {
            sql.append(" WHERE ID ").append("IN (");
            int colIndex = 0;
            for(long id : ids) {
                if(colIndex > 0) {
                    sql.append(", ");
                }
                sql.append(id);
                colIndex++;
            }
            sql.append(" )");
        }
        return sql.toString();
    }

    /**
     * buid select SQL by column
     * @param entityClass
     * @param columns
     * @return
     */
    public static String buildSelectByColumnSQL(Class<?> entityClass, String... columns){
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName);
        if(columns.length > 0) {
            sql.append(" WHERE ");
            int colIndex = 0;
            for(String column : columns) {
                if(!EntityRepository.getInstance().isColumnExisted(entityClass, column)){
                    log.warn("Column " + column + " doesn't exist in Table " + tableName);
                }
                if(colIndex > 0) {
                    sql.append(" AND ");
                }
                sql.append(column).append(" = $").append(column.toUpperCase());
                colIndex++;
            }
        }
        return sql.toString();
    }

/*
    public static String buildSelectLikeByColumnSQL(Class<?> entityClass, String column){
        String tableName = getTableName(entityClass);
        if(!EntityRepository.getInstance().isColumnExisted(entityClass, column)){
            log.warn("Column " + column + " doesn't exist in Table " + tableName);
        }        
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName).append(" WHERE ").append(column).append(" LIKE ").append("$").append(column.toUpperCase());
        return sql.toString();
    }
*/

    /**
     * build select sql by id column
     * @param entityClass
     * @return
     */
    public static String buildSelectByIdSQL(Class<?> entityClass){
        String pkColumnName = "ID"; // default use ID column as PK
        ColumnMetadata pkColumnMetadata = EntityRepository.getInstance().getPKColumnMetadata(entityClass);
        if(pkColumnMetadata != null) {
            pkColumnName = pkColumnMetadata.getName();
        }
        return buildSelectByColumnSQL(entityClass, pkColumnName);
    }

    public static String buildSelectAllSQL(Class<?> entityClass){
        return buildSelectByColumnSQL(entityClass);
    }

    public static String buildSelectInByIdSQL(Class<?> entityClass, long... ids){
        String pkColumnName = "ID";
        ColumnMetadata pkColumnMetadata = EntityRepository.getInstance().getPKColumnMetadata(entityClass);
        if(pkColumnMetadata != null) {
            pkColumnName = pkColumnMetadata.getName();
        }
        String[] colValues = new String[ids.length];
        for(int i=0; i<ids.length; i++){
            colValues [i] = ids[i] + "";
        }
        return buildSelectInByColumnSQL(entityClass, pkColumnName, colValues );
    }

    public static String buildSelectInByColumnSQL(Class<?> entityClass, String column, String... colValues){
        String tableName = getTableName(entityClass);
        if(!EntityRepository.getInstance().isColumnExisted(entityClass, column)){
            log.warn("Column " + column + " doesn't exist in Table " + tableName);
        }

        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName);
        if(colValues.length > 0) {
            sql.append(" WHERE ").append(column).append(" IN (");
            int colIndex = 0;
            for(String colValue : colValues) {
                if(colIndex > 0) {
                    sql.append(" , ");
                }
                sql.append(colValue);
                colIndex++;
            }
            sql.append(" )");
        }
        return sql.toString();
    }

    /**
     * generate Select sql conditionally 
     * @param entityClass
     * @param condition
     * @return
     */
    public static String buildSelectByConditoinSQL(Class<?> entityClass, Condition condition) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT * FROM ").append(tableName);
        if(condition != null) {
            sql.append(" WHERE ").append(condition.getSQLTemplateString());
        }
        return sql.toString();
    }

    /**
     * generator Count(*) sql 
     */
    public static String buildCountSQL(Class<?> entityClass) {
        return buildCountByConditionSQL(entityClass, null);
    }

    /**
     * generator Count(*) sql by given condition 
     */
    public static String buildCountByConditionSQL(Class<?> entityClass, Condition condition) {
        String tableName = getTableName(entityClass);
        StringBuffer sql = new StringBuffer("SELECT COUNT(*) FROM ").append(tableName);
        if(condition != null) {
            sql.append(" WHERE ").append(condition.getSQLTemplateString());
        }
        return sql.toString();
    }

    /**
     * try to get table name according to entityClas
     */
    public static String getTableName(Class<?> entityClass) {
        return EntityRepository.getInstance().getEntityMetadata(entityClass).getTableName();
    }

    public static void main(String[] args) {
        
    }
}
